-- @owner: zhanghuan
-- @date: 2024-06-19
-- @testpoint: insert into /delete/update

--step1:创建测试表; expect:执行成功
drop table if exists t_ustore_listagg_0044_01;
create table t_ustore_listagg_0044_01(
     col_1 integer,
     col_2 bigint,
     col_3 float8,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 smalldatetime,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);

--step2:创建序列; expect:执行成功
drop sequence if exists seq_ustore_listagg_0044_01;
create sequence seq_ustore_listagg_0044_01 increment by 1 start with 10;

--step3:向表中插入数据; expect:执行成功
insert into t_ustore_listagg_0044_01 values(1,seq_ustore_listagg_0044_01.nextval,1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0044_01 values(1,seq_ustore_listagg_0044_01.nextval,1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0044_01 values(1,seq_ustore_listagg_0044_01.nextval,1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0044_01 values(1,seq_ustore_listagg_0044_01.nextval,1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
 to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0044_01 values(1,seq_ustore_listagg_0044_01.nextval,1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0044_01 values(1,seq_ustore_listagg_0044_01.nextval,1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e')::blob,(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),to_timestamp('2019-01-03','yyyy-mm-dd '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');

--step4:insert into /delete/update; expect:执行成功
select col_1,col_2,listagg(3,15) within group (order by col_1 desc ,col_2 desc,col_1,col_1 asc,col_1,col_1,col_1 desc,col_2,col_2,col_2) col_3 from t_ustore_listagg_0044_01 group by col_1,col_2 order by 1,2,3;
drop table if exists t_ustore_listagg_0044_02;
create table t_ustore_listagg_0044_02(q int,w int,e decimal(10,2)) with (storage_type=ustore);
insert into t_ustore_listagg_0044_02 select col_1,col_2,listagg(3,15) within group (order by col_1 desc ,col_2 desc,col_1,col_1 asc,col_1,col_1,col_1 desc,col_2,col_2,col_2) col_3 from t_ustore_listagg_0044_01 group by col_1,col_2 order by 1,2,3;

delete from t_ustore_listagg_0044_02 where (q,w,e) in (select col_1,col_2,listagg(3,15) within group (order by col_1 desc ,col_2 desc,col_1,col_1 asc,col_1,col_1,col_1 desc,col_2,col_2,col_2) col_3 from t_ustore_listagg_0044_01 group by col_1,col_2 order by 1,2,3);

insert into t_ustore_listagg_0044_02(e) values(2);

update t_ustore_listagg_0044_02 set e=(select listagg(0.31) within group (order by e) a from t_ustore_listagg_0044_02 order by 1 limit 1);
select * from t_ustore_listagg_0044_02 order by 1,2,3;

--step5:清理环境; expect:执行成功
drop sequence if exists seq_ustore_listagg_0044_01;
drop table if exists t_ustore_listagg_0044_01;
drop table if exists t_ustore_listagg_0044_02;

